"""
Copyright (c) 2022 Huawei Technologies Co.,Ltd.

openGauss is licensed under Mulan PSL v2.
You can use this software according to the terms and conditions of the Mulan PSL v2.
You may obtain a copy of Mulan PSL v2 at:

          http://license.coscl.org.cn/MulanPSL2

THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
See the Mulan PSL v2 for more details.
"""
"""
Case Type   : 导入导出、备份恢复工具支持M*兼容性
Case Name   : create function语句，导出再导入
Create At   : 2023.05.23
Owner       : z@wan005
Description :
    1.创建兼容b库
    2.创建用户
    3.创建兼容M*自定义函数，含参数language sql、[not] deterministic、contains sql、
    no sql、reads sql data、modifies sql data、sql security、modifies sql data、
    sql security、definer
    4.创建自定义函数，存储体含repeat
    5.创建自定义函数，存储体含declare cursor
    6.创建自定义函数，存储体含declare cursor
    7.创建自定义函数，存储体为do语句
    8.创建自定义函数，存储体含iterate label
    9.创建自定义函数，存储体含case when condition
    10.创建自定义函数，存储体含while语句
    11.使用gs_dump工具导出表为自定义归档格式
    12.gs_restore导入
    13.连接导入库使用show create function语句查询自定义函数
    14.清理环境
Expect      :
    1.成功
    2.成功
    3.成功
    4.成功
    5.成功
    6.成功
    7.成功
    8.成功
    9.成功
    10.成功
    11.成功
    12.成功
    13.成功
    14.成功
History     :
"""

import os
import unittest

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node
from yat.test import macro


class GsDump0061(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.sh_primary = CommonSH('PrimaryDbUser')
        self.dbuser_node = Node('PrimaryDbUser')
        self.constant = Constant()
        self.db_name = "db_dump_0061"
        self.user_name = "user_dump_0061"
        self.func_name = "func_dump_0061"
        self.tb_name = "tb_dump_0061"
        self.dump_path = os.path.join(macro.DB_BACKUP_PATH, 'dump0061')

    def test_server_tools(self):
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        text = '----step1: 创建兼容b库 expect: 成功----'
        self.log.info(text)
        sql_cmd = f"drop database if exists {self.db_name};" \
                  f"drop database if exists {self.db_name}_01;" \
                  f"create database {self.db_name} dbcompatibility 'b';" \
                  f"create database {self.db_name}_01 dbcompatibility 'b';"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd)
        self.log.info(sql_result)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, sql_result,
                      '执行失败:' + text)

        text = '--step2: 创建用户 expect: 成功--'
        self.log.info(text)
        sql_cmd = f"drop user if exists {self.user_name};" \
                  f"create user {self.user_name} password " \
                  f"'{macro.PASSWD_INITIAL}';"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertIn(self.constant.CREATE_ROLE_SUCCESS_MSG, sql_result,
                      '执行失败:' + text)

        text = '--step3: 创建兼容M*自定义函数，含参数language sql、' \
               '[not] deterministic、contains sql、no sql、reads sql data、' \
               'modifies sql data、sql security、modifies sql data、' \
               'sql security、definer expect: 成功--'
        self.log.info(text)
        sql_cmd = f'''drop function if exists {self.func_name};
        create definer = {self.user_name} function {self.func_name}() \
        returns void language plpgsql
            BEGIN
             return\;
            END;
        drop function if exists {self.func_name}_01;
        create definer = {self.user_name} function {self.func_name}_01() \
        returns void deterministic
            BEGIN
             return\;
            END;
        drop function if exists {self.func_name}_02;
        create definer = {self.user_name} function {self.func_name}_02() \
        returns void not deterministic
            BEGIN
            return\;
             END;
        drop function if exists {self.func_name}_03;
        create definer = {self.user_name} function {self.func_name}_03() \
        returns void contains sql
            BEGIN
            return\;
            END;
        drop function if exists {self.func_name}_04;
        create definer = {self.user_name} function {self.func_name}_04() \
        returns void no sql
            BEGIN
            return\;
            END;
        drop function if exists {self.func_name}_05;
        create definer = {self.user_name} function  {self.func_name}_05() \
        returns void reads sql data
            BEGIN
            return\;
            END;
        drop function if exists {self.func_name}_06;
        create definer = {self.user_name} function {self.func_name}_06() \
        returns void modifies sql data
            BEGIN
            return\;
            END;
        drop function if exists {self.func_name}_07;
        create definer = {self.user_name} function {self.func_name}_07() \
        returns void sql security definer
            BEGIN
            return\;
            END;
        drop function if exists {self.func_name}_08;
        create definer = {self.user_name} function {self.func_name}_08() \
        returns void sql security invoker
            BEGIN
            return\;
            END;
        drop function if exists {self.func_name}_09;
        create definer = {self.user_name} function {self.func_name}_09() \
        returns void
            BEGIN
            return\;
            END;'''
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertEqual(10, sql_result.count(
            self.constant.CREATE_FUNCTION_SUCCESS_MSG), '执行失败:' + text)

        text = '--step4: 创建自定义函数，存储体含repeat expect: 成功'
        self.log.info(text)
        sql_cmd = f'''drop function if exists {self.func_name}_10;
        create definer = {self.user_name} function \
        {self.func_name}_10(int) returns void
            AS
            \$\$
            DECLARE
                    i int = 0;
            BEGIN
                    repeat
                            i = i + 1;
                            until i > \$1
                    end repeat;
            END;
            \$\$ language plpgsql immutable;
        drop function if exists {self.func_name}_11;
        create definer = {self.user_name} function {self.func_name}_11(int) \
        returns void
            AS
            \$\$
            DECLARE
                    i int = 0;
            BEGIN
                    <<label>> repeat
                            i = i + 1;
                            until i > \$1
                    end repeat label;
            END;
            \$\$ language plpgsql immutable;'''
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertEqual(2, sql_result.count('CREATE FUNCTION'),
                         '执行失败:' + text)

        text = '--step5: 创建自定义函数，存储体含declare cursor expect: 成功'
        self.log.info(text)
        sql_cmd = f'''drop table if exists {self.tb_name};
        create table {self.tb_name}(name varchar(100), loc varchar(100), \
        no integer);
        insert {self.tb_name} values('macrosoft', 'usa', 001);
        insert {self.tb_name} values('oracle', 'usa', 002);
        insert {self.tb_name} values('backbery', 'canada', 003);
        drop function if exists {self.func_name}_12;
        create definer = {self.user_name} function {self.func_name}_12 returns \
        void
            AS
            \$\$
            DECLARE
                    company_name varchar(100);
                    company_loc varchar(100);
                    company_no integer;
            begin
                    declare c1_all cursor is
                            select name, loc, no from company order by 1, 2, 3;
                    if not c1_all%isopen then open c1_all;
                    end if;
                    loop
                            fetch c1_all into company_name, company_loc, \
                            company_no;
                            exit when c1_all%notfound;
                            raise notice '% : % : %', company_name, \
                            company_loc, company_no;
                    end loop;
                    if c1_all%isopen then
                            close c1_all;
                    end if;
            end;
            \$\$ language plpgsql immutable;'''
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, sql_result,
                      '执行失败:' + text)
        self.assertEqual(3, sql_result.count('INSERT 0 1'), '执行失败:' + text)
        self.assertIn(self.constant.CREATE_FUNCTION_SUCCESS_MSG, sql_result,
                      '执行失败:' + text)

        text = '--step6: 创建自定义函数，存储体含declare cursor expect: 成功'
        self.log.info(text)
        sql_cmd = f'''drop function if exists {self.func_name}_13;
        create definer = {self.user_name} function \
        {self.func_name}_13 returns text
            AS
            \$\$
            BEGIN
                    declare DISVISION_ZERO condition for 1;
                    return 'declare condition successed';
            END;
            \$\$ language plpgsql immutable;'''
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertIn(self.constant.CREATE_FUNCTION_SUCCESS_MSG, sql_result,
                      '执行失败:' + text)

        text = '--step7: 创建自定义函数，存储体为do语句 expect: 成功'
        self.log.info(text)
        sql_cmd = f'''drop function if exists {self.func_name}_14;
        create definer = {self.user_name} function \
        {self.func_name}_14 returns void
            AS
            \$\$
            DECLARE
                x,y,z int =1;
            BEGIN
                y := 2;
                z := 4;
                do x + x;
                do y * z;
                do x + x, y * z;
                do sin(x);
                do pg_sleep(1);
            END;
            \$\$ language plpgsql immutable;'''
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertIn(self.constant.CREATE_FUNCTION_SUCCESS_MSG, sql_result,
                      '执行失败:' + text)

        text = '--step8: 创建自定义函数，存储体含iterate label expect: 成功'
        self.log.info(text)
        sql_cmd = f'''drop function if exists {self.func_name}_15;
        create definer = {self.user_name} function \
        {self.func_name}_15(int) returns int
            AS
            \$\$
            BEGIN
                    label1: loop
                    \$1 := \$1 + 1;
                    if \$1 < 10 then iterate label1;
                    end if;
                    leave label1;
                    end loop label1;
                    return \$1;
            END;
            \$\$ language plpgsql immutable;
        drop function if exists {self.func_name}_16;
        create definer = {self.user_name} function \
        {self.func_name}_16(int) returns int
            AS
            \$\$
            BEGIN
                    loop
                            \$1 := \$1 + 1;
                            if \$1 < 10 then iterate;
                            end if;
                            leave;
                    end loop;
                    return \$1;
            END;
            \$\$ language plpgsql immutable;'''
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertEqual(2, sql_result.count(
            self.constant.CREATE_FUNCTION_SUCCESS_MSG), '执行失败:' + text)

        text = '--step9: 创建自定义函数，存储体含case when condition expect: 成功'
        self.log.info(text)
        sql_cmd = f'''drop function if exists {self.func_name}_17;
        create definer = {self.user_name} function \
        {self.func_name}_17(int) returns text
            AS
            \$\$
            BEGIN
                    case \$1
                            when 1 then
                                    return 'one';
                            when 2 then
                                    return 'two';
                            when 3 then
                                    return 'three';
                            else
                                    return 'more than three';
                    end case;
            END;
            \$\$ language plpgsql immutable;'''
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertIn(self.constant.CREATE_FUNCTION_SUCCESS_MSG, sql_result,
                      '执行失败:' + text)

        text = '--step10: 创建自定义函数，存储体含while语句 expect: 成功'
        self.log.info(text)
        sql_cmd = f'''drop function if exists {self.func_name}_18;
        create definer = {self.user_name} function \
        {self.func_name}_18(int) returns int
            AS
            \$\$
            BEGIN
                    while \$1 < 10
                            do \$1 := \$1 + 1;
                    end while;
                    return \$1;
            END;
            \$\$ language plpgsql immutable;
        drop function if exists {self.func_name}_19;
        create definer = {self.user_name} function \
        {self.func_name}_19(int) returns int
            AS
            \$\$
            BEGIN
                    label: while \$1 < 10
                            do \$1 := $1 + 1;
                    end while label;
                    return \$1;
            END;
            \$\$ language plpgsql immutable;'''
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertEqual(2, sql_result.count(
            self.constant.CREATE_FUNCTION_SUCCESS_MSG), '执行失败:' + text)

        text = '----step11: 使用gs_dump工具导出表为自定义归档格式 expect: 成功----'
        self.log.info(text)
        gs_dump_cmd = f"source {macro.DB_ENV_PATH};" \
                      f"gs_dump " \
                      f"-p {self.dbuser_node.db_port} " \
                      f"{self.db_name} -F c " \
                      f"-f {self.dump_path}"
        self.log.info(gs_dump_cmd)
        dump_msg = self.dbuser_node.sh(gs_dump_cmd).result()
        self.log.info(dump_msg)
        self.assertIn(f"dump database {self.db_name} successfully",
                      dump_msg, '执行失败:' + text)

        text = '----step12: gs_restore导入 expect: 成功----'
        self.log.info(text)
        gsql_cmd = f"source {macro.DB_ENV_PATH};" \
                   f"gs_restore " \
                   f"-p {self.dbuser_node.db_port} " \
                   f"-d {self.db_name}_01 " \
                   f"{self.dump_path}"
        self.log.info(gsql_cmd)
        gsql_msg = self.dbuser_node.sh(gsql_cmd).result()
        self.log.info(gsql_msg)
        self.assertIn(self.constant.RESTORE_SUCCESS_MSG, gsql_msg,
                      '执行失败:' + text)

        text = '----step13: 连接导入库使用show create function语句查询自定义函数 ' \
               'expect: 20个函数导入成功----'
        self.log.info(text)
        sql_cmd = f"show function status like '%{self.func_name}%';"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=f'{self.db_name}_01')
        self.log.info(sql_result)
        self.assertIn('20 rows', sql_result, '执行失败' + text)
        self.log.info('调用某个函数')
        sql_cmd = f"call {self.func_name}_18(10);"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=f'{self.db_name}_01')
        self.log.info(sql_result)
        self.assertEqual('10', sql_result.splitlines()[-2].strip(),
                         '执行失败' + text)

    def tearDown(self):
        text = '---step14: 清理环境 expect: 成功----'
        self.log.info(text)
        sql_cmd = f"drop database if exists {self.db_name};" \
                  f"drop database if exists {self.db_name}_01;" \
                  f"drop user if exists {self.user_name};"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname='postgres')
        self.log.info(sql_result)
        rm_cmd = f"rm -rf {self.dump_path}"
        self.log.info(rm_cmd)
        rm_msg = self.dbuser_node.sh(rm_cmd).result()
        self.log.info(rm_msg)
        self.assertEqual(2,
                         sql_result.count(self.constant.DROP_DATABASE_SUCCESS),
                         '执行失败:' + text)
        self.assertIn('DROP ROLE', sql_result, '执行失败:' + text)
        self.assertEqual('', rm_msg)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
